package com.ztjava.core.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.OfficeXmlFileException;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jfinal.kit.Ret;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.ehcache.CacheKit;
import com.jfinal.upload.UploadFile;
import com.ztjava.core.Constant;

public class ExeclUtil {
	
	//读取数据字典
	public static Map<String,Map<String,String>> loadBaseData(String str){
		Map<String,Map<String,String>> map = new HashMap<String, Map<String,String>>();
		if(str.equals("")){
			return map;
		}
		JSONArray basedate = JSONObject.parseArray(str);
		
		JSONObject code = null;
		JSONArray items = null;
		JSONObject vo = null;
		for(int i=0;i<basedate.size();i++){
			code = basedate.getJSONObject(i);
			Map<String,String> sub = new HashMap<String,String>();
			items = code.getJSONArray("items");
			for(int j=0;j<items.size();j++){
				vo = items.getJSONObject(j);
				sub.put(vo.getString("name"), vo.getString("value"));
			}
			map.put(code.getString("code"), sub);
		}
		return map;
	}
	
	public static File dateToExecl(List<Record> list,String exportConfig){
    	File file = new File(DateUtil.formatDateToStr("yyyyMMddHHmmss", new Date())+".xls");  
		HSSFWorkbook hssfWorkbook = new HSSFWorkbook();  
		HSSFSheet hssfSheet = hssfWorkbook.createSheet("sheet1"); 
		//创建表头
		HSSFRow row = hssfSheet.createRow(0);  
		HSSFCell cell = null;  
		List<Record> columns = CacheKit.get(Constant.CacheData, exportConfig);
        for (int i = 0; i < columns.size(); i++) {  
            cell = row.createCell(i);  
            cell.setCellValue(columns.get(i).getStr("name"));
        }  
        Record record = null; 
        Record column = null;
        String value = "";
		Integer count = 0;
    	for (int i = 0; i < list.size(); i++) {  
    		row = hssfSheet.createRow(++count);  
    		record = list.get(i);  
            for (int j = 0; j < columns.size(); j++) {  
            	column = columns.get(j);
                cell = row.createCell(j);  
                value = record.get(column.getStr("field"))==null?"-":record.get(column.getStr("field")).toString();
                if(column.get("change")!=null&&!column.getStr("change").equals("")){
                	Map<String,Record> map = CacheKit.get(Constant.CacheData,column.getStr("change"));
                	if(map.get(value)!=null){
                		value = map.get(value).getStr("name");
                	}
                }
                cell.setCellValue(value);  
            } 
    	}
        try {  
            FileOutputStream fileOutputStreane = new FileOutputStream(file);  
            hssfWorkbook.write(fileOutputStreane);  
            fileOutputStreane.flush();  
            fileOutputStreane.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  
        }  
        return file;
	}
	
	public static Ret execlToList(UploadFile uploadFile,String importCode,String basedate,Integer user_id,String repeatDeal,String repeatWhere) throws Exception{
		Ret ret = Ret.create();

		//获取表格数据
		HSSFSheet sheet = null;
		HSSFWorkbook wookbook = null;
		try{
			wookbook = new HSSFWorkbook(new FileInputStream(uploadFile.getFile()));
			sheet = wookbook.getSheetAt(0);
		}catch (OfficeXmlFileException e) {
			ret.put("result", "error");
			ret.put("message", "必须使用xls格式的execl文档！");
			return ret;
		}catch (Exception e) {
			e.printStackTrace();
			ret.put("result", "error");
			ret.put("message", "导入时出错！");
			return ret;
		}finally{
			if(wookbook!=null)
				wookbook.close();
		}
		if(sheet.getRow(0).getPhysicalNumberOfCells()==0){
			ret.put("result", "error");
			ret.put("message", "execl为空！");
			return ret;
		}
		if(sheet.getPhysicalNumberOfRows()<=1){
			ret.put("result", "error");
			ret.put("message","没有可导入的数据！");
			return ret;
		}

		Record improtConfig = CacheKit.get(Constant.CacheData, importCode);
		if(improtConfig==null){
			ret.put("result", "error");
			ret.put("message","缺少导入配置！");
			return ret;
		}
		String tablename = improtConfig.getStr("tablename");
		if(tablename.equals("")){
			ret.put("result", "error");
			ret.put("message","导入数据表不能为空！");
			return ret;
		}
		List<Record> columnList = improtConfig.get("columns");
		
		//基础数据
		Map<String,Map<String,String>> map = loadBaseData(basedate);
		//重复数据
		Map<String,Map<String,String>> repeatMap = new HashMap<String, Map<String,String>>();
		
		//查找所在列序号
		Integer num = sheet.getRow(0).getPhysicalNumberOfCells();
		Record column = null;
		Map<String,String> database = null;
		HSSFRow firstRow = sheet.getRow(0);
		for(int i=0;i<columnList.size();i++) {
			column = columnList.get(i);
			for(int j=0;j<num;j++){
				if(readCellToString(firstRow.getCell(j)).toString().trim().equals(column.getStr("name"))){
					columnList.get(i).set("index", j);
					break;
				}
			}
		}
		
		List<Record> result = new ArrayList<Record>();
		HSSFCell cell = null;
		Integer rows = sheet.getPhysicalNumberOfRows();
		String value = "";
		Integer success_num = 0;
		Integer continue_num = 0;
		for (int i = 1; i < rows; i++) {
			Record record = new Record();
			HSSFRow row = sheet.getRow(i);
			if(is_blank(row)) break;
			boolean is_next = false;
			for(int j=0;j<columnList.size();j++) {
				column = columnList.get(j);
				//字段是否存在验证
				if(column.getBoolean("must_have")&&column.get("index")==null){
					ret.put("result", "error");
					ret.put("message","“"+column.getStr("name")+"”数据列必须存在！");
					return ret;
				}
				if(column.get("index")==null){
					continue;
				}
				cell = row.getCell(column.getInt("index"));
				value = readCellToString(cell);
				//是否为空验证
				if(!column.getBoolean("blank")){
					if(value==null||value.equals("")){
						ret.put("result", "error");
						ret.put("message","第"+(i+1)+"行的“"+column.getStr("name")+"”数据不能为空！");
						return ret;
					}
				}
				//日期格式验证
				if(column.get("date")!=null&&value!=null&&!value.equals("")){
					if(DateUtil.formStrToDate(column.getStr("date"), value)==null){

						ret.put("result", "error");
						ret.put("message","第"+(i+1)+"行的“"+column.getStr("name")+"”日期要求格式为“"+column.getStr("date")+"”(请使用文本格式)！");
						return ret;
					}
				}
				//数据替换
				if(column.get("change")!=null&&value!=null&&!value.equals("")){
					database = map.get(column.getStr("change"));
					if(database==null&&CacheKit.get(Constant.CacheData, column.get("change"))!=null){
						database = new HashMap<String,String>();
						Map<String,Record> tempbase =  CacheKit.get(Constant.CacheData, column.get("change"));
						for(String key:tempbase.keySet()){
							database.put(tempbase.get(key).getStr("name"), key);
						}
						map.put(column.getStr("change"), database);
					}
					if(database==null){
						ret.put("result", "error");
						ret.put("message","第"+(i+1)+"行的“"+column.getStr("name")+"”数据转换代码不存在！");
						return ret;
					}
					value = database.get(value);
					if(value==null){
						ret.put("result", "error");
						ret.put("message","第"+(i+1)+"行的“"+column.getStr("name")+"”数据无法转换！");
						return ret;
					}
				}

				//重复数据验证
				if(!column.getBoolean("repeat")&&value!=null&&!value.equals("")){
					Map<String,String> sub = repeatMap.get(column.getStr("field"));
					if(sub==null){
						sub = new HashMap<String,String>();
						List<Record> list = Db.find("select "+column.getStr("field")+" from "+tablename+repeatWhere);
						for(int m=0;m<list.size();m++){
							sub.put(list.get(m).getStr(column.getStr("field")), "T");
						}
					}
					if(sub.get(value)!=null){
						if(repeatDeal.equals("notice")){
							ret.put("result", "error");
							ret.put("message","第"+(i+1)+"行的“"+column.getStr("name")+"”数据重复！");
							return ret;
						}else {
							is_next = true;
							break;
						}
					}
					sub.put(value, "T");
					repeatMap.put(column.getStr("field"), sub);
				}
				//长度上限验证
				if(column.get("max")!=null&&value!=null&&!value.equals("")){
					if(value.length()>column.getInt("max")) {
						ret.put("result", "error");
						ret.put("message","第"+(i+1)+"行的“"+column.getStr("name")+"”长度不能大于"+column.getInt("max")+"！");
						return ret;
					}
				}
				//长度下限验证
				if(column.get("min")!=null&&value!=null&&!value.equals("")){
					if(value.length()<column.getInt("min")) {
						ret.put("result", "error");
						ret.put("message","第"+(i+1)+"行的“"+column.getStr("name")+"”长度不能小于"+column.getInt("min")+"！");
						return ret;
					}
				}
				record.set(column.getStr("field"), value);
			}
			if(is_next){
				continue_num = continue_num + 1;
				continue;
			}
			success_num = success_num + 1;
			record.set("create_user",user_id);
			result.add(record);
		}
		ret.put("result", "success");
		ret.put("list",result);
		ret.put("success_num",success_num);
		ret.put("continue_num",continue_num);
		return ret;
	}
	
	//读取单元格内容
	public static String readCellToString(HSSFCell cell){
		if (cell != null) {
            switch (cell.getCellType()) {
            case HSSFCell.CELL_TYPE_NUMERIC:
            	//强成文本格式，再读取
            	cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                return cell.getStringCellValue().trim();
            case HSSFCell.CELL_TYPE_STRING:
            	return cell.getStringCellValue().trim();
            default:
            	return null;
            }
        }
		return null;		
	}
	public static boolean is_blank (HSSFRow row){
		if(row.getCell(0)==null||readCellToString(row.getCell(0))==null||readCellToString(row.getCell(0)).toString().length()==0) {
			if(row.getCell(1)==null||readCellToString(row.getCell(1))==null||readCellToString(row.getCell(1)).toString().length()==0) {
				return true;
			}
		}
		return false;
	}
}
